{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''数据清洗'''\n",
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "DATA_PRE_DIR = './datasets/radar'\n",
    "DATA_AFTER_DIR = './datasets/cache'\n",
    "\n",
    "FEATURES_COLUMNS = ['航班号(I170)', '经纬度(I130)', '系统接收时间', '地速(I160)', '几何高度(I140)', '航向(I160)']\n",
    "FEATURES_COLUMNS = ['航班号(I170)', '高精度经纬度(I131)', '高精度位置报接收时间(I073&I074)', ]\n",
    "\n",
    "\n",
    "def get_longitude(value: str, **extra):\n",
    "    if value is None or value is pd.NA or value.strip() == '':\n",
    "        return pd.NA\n",
    "    return float(value.split(',')[0])\n",
    "\n",
    "\n",
    "def get_latitude(value: str, **extra):\n",
    "    if value is None or value is pd.NA or value.strip() == '':\n",
    "        return pd.NA\n",
    "    return float(value.split(',')[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: './datasets/cache\\\\20210401.txt'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-39-4263a3609001>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[0mp\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mDATA_PRE_DIR\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'20210401.txt'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mDataFrame\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mread_table\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mp\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0msep\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'\\t'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'UTF-8'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      3\u001b[0m \u001b[1;31m# 空串替换为NA\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreplace\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mto_replace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34mr'^\\s*$'\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mNA\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mregex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0minplace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mTrue\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;31m# 0.00替换为NA\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mread_table\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[0;32m    765\u001b[0m         \u001b[1;31m# default to avoid a ValueError\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    766\u001b[0m         \u001b[0msep\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\",\"\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 767\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0mread_csv\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mlocals\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    768\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    769\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36mread_csv\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, dialect, error_bad_lines, warn_bad_lines, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[0;32m    686\u001b[0m     )\n\u001b[0;32m    687\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 688\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    689\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    690\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    452\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    453\u001b[0m     \u001b[1;31m# Create the parser.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 454\u001b[1;33m     \u001b[0mparser\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mTextFileReader\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfp_or_buf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwds\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    455\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    456\u001b[0m     \u001b[1;32mif\u001b[0m \u001b[0mchunksize\u001b[0m \u001b[1;32mor\u001b[0m \u001b[0miterator\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, f, engine, **kwds)\u001b[0m\n\u001b[0;32m    946\u001b[0m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"has_index_names\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    947\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 948\u001b[1;33m         \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    949\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    950\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mclose\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m_make_engine\u001b[1;34m(self, engine)\u001b[0m\n\u001b[0;32m   1178\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m_make_engine\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"c\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1179\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"c\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1180\u001b[1;33m             \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_engine\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mCParserWrapper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0moptions\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1181\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1182\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mengine\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;34m\"python\"\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mf:\\work\\track-prediction\\.venv\\lib\\site-packages\\pandas\\io\\parsers.py\u001b[0m in \u001b[0;36m__init__\u001b[1;34m(self, src, **kwds)\u001b[0m\n\u001b[0;32m   1991\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mkwds\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mget\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"compression\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mNone\u001b[0m \u001b[1;32mand\u001b[0m \u001b[0mencoding\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1992\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1993\u001b[1;33m                 \u001b[0msrc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mopen\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"rb\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1994\u001b[0m                 \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mhandles\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0msrc\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1995\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: './datasets/cache\\\\20210401.txt'"
     ]
    }
   ],
   "source": [
    "p = os.path.join(DATA_PRE_DIR, '20210401.txt')\n",
    "df: pd.DataFrame = pd.read_table(p, sep='\\t', encoding='UTF-8')\n",
    "# 空串替换为NA\n",
    "df.replace(to_replace=r'^\\s*$', value=pd.NA, regex=True, inplace=True)\n",
    "# 0.00替换为NA\n",
    "df.replace(to_replace=r'^\\s*?0*?\\.?0*?\\s*?$', value=pd.NA, regex=True, inplace=True)\n",
    "# 无航班号和时间的删除\n",
    "df.dropna(axis=0, how='any', subset=['航班号(I170)', '系统接收时间'], inplace=True)\n",
    "df = df.loc[:, FEATURES_COLUMNS].copy(deep=True)\n",
    "# 重命名\n",
    "df.rename(columns={\n",
    "    '系统接收时间': '时间',\n",
    "    '航班号(I170)': '航班号',\n",
    "    '地速(I160)': '速度',\n",
    "    '几何高度(I140)': '高度',\n",
    "    '航向(I160)': '航向'\n",
    "}, inplace=True)\n",
    "# 经纬度提取\n",
    "df['经度'] = df.loc[:, '经纬度(I130)'].apply(get_longitude)\n",
    "df['纬度'] = df.loc[:, '经纬度(I130)'].apply(get_latitude)\n",
    "df.drop(['经纬度(I130)'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>航班号</th>\n",
       "      <th>时间</th>\n",
       "      <th>速度</th>\n",
       "      <th>高度</th>\n",
       "      <th>航向</th>\n",
       "      <th>经度</th>\n",
       "      <th>纬度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 16:00:28.872</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 16:00:28.872</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 16:00:28.872</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 16:00:28.873</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>665</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 16:00:30.874</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564248</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 19:53:35.408</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564333</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 19:53:36.413</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564334</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 19:53:36.413</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564335</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 19:53:36.413</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1564336</th>\n",
       "      <td>TEST002</td>\n",
       "      <td>2020-03-31 19:53:36.414</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55264 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              航班号                       时间    速度    高度    航向    经度    纬度\n",
       "128      TEST002   2020-03-31 16:00:28.872  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "129      TEST002   2020-03-31 16:00:28.872  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "130      TEST002   2020-03-31 16:00:28.872  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "131      TEST002   2020-03-31 16:00:28.873  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "665      TEST002   2020-03-31 16:00:30.874  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "...           ...                      ...   ...   ...   ...   ...   ...\n",
       "1564248  TEST002   2020-03-31 19:53:35.408  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "1564333  TEST002   2020-03-31 19:53:36.413  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "1564334  TEST002   2020-03-31 19:53:36.413  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "1564335  TEST002   2020-03-31 19:53:36.413  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "1564336  TEST002   2020-03-31 19:53:36.414  <NA>  <NA>  <NA>  <NA>  <NA>\n",
       "\n",
       "[55264 rows x 7 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pnames = set(df['航班号'].to_list())\n",
    "dt = df.loc[df['航班号'] == 'TEST002 ']\n",
    "dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_bak = df.copy(deep=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>航班号</th>\n",
       "      <th>纬度</th>\n",
       "      <th>经度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [航班号, 纬度, 经度]\n",
       "Index: []"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[1564335:1564340, ('航班号', '纬度', '经度')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.interpolate(method='time', axis=0, limit=2, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[1564335:1564340, ('航班号', '经度')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dt = df.loc[df['航班号'] == 'TEST002 ']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all(dt['速度'].isna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "'''数据清洗'''\n",
    "import pandas as pd\n",
    "import os\n",
    "from datetime import datetime\n",
    "\n",
    "DATA_PRE_DIR = './datasets/cache'\n",
    "DATA_AFTER_DIR = './datasets/train'\n",
    "\n",
    "FEATURES_COLUMNS = ['航班号', '时间', '经度', '纬度', '速度', '高度', '航向']\n",
    "\n",
    "\n",
    "for p in os.listdir(DATA_PRE_DIR):\n",
    "    p = os.path.join(DATA_PRE_DIR, p)\n",
    "    # TODO 归一化\n",
    "    df = pd.read_csv(p, sep=',')\n",
    "    break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_datetime_numric(value: datetime, **extra):\n",
    "    return value.timestamp()\n",
    "\n",
    "df['时间'] = df.loc[:, '时间'].apply(pd.to_datetime, errors='raise', format='%Y-%m-%d %H:%M:%S.%f')\n",
    "df['时间'] = df.loc[:, '时间'].apply(convert_datetime_numric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>时间</th>\n",
       "      <th>航班号</th>\n",
       "      <th>经度</th>\n",
       "      <th>纬度</th>\n",
       "      <th>速度</th>\n",
       "      <th>高度</th>\n",
       "      <th>航向</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.585674e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>112.957840</td>\n",
       "      <td>38.014561</td>\n",
       "      <td>726.38</td>\n",
       "      <td>7193.28</td>\n",
       "      <td>244.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.585674e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>112.957840</td>\n",
       "      <td>38.014561</td>\n",
       "      <td>726.38</td>\n",
       "      <td>7193.28</td>\n",
       "      <td>244.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.585674e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>112.953896</td>\n",
       "      <td>38.013103</td>\n",
       "      <td>726.38</td>\n",
       "      <td>7193.28</td>\n",
       "      <td>244.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.585674e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>112.953896</td>\n",
       "      <td>38.013103</td>\n",
       "      <td>726.38</td>\n",
       "      <td>7193.28</td>\n",
       "      <td>244.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.585674e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>112.949966</td>\n",
       "      <td>38.011600</td>\n",
       "      <td>726.38</td>\n",
       "      <td>7193.28</td>\n",
       "      <td>244.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38166</th>\n",
       "      <td>1.585678e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>108.765274</td>\n",
       "      <td>34.431236</td>\n",
       "      <td>0.41</td>\n",
       "      <td>449.58</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38167</th>\n",
       "      <td>1.585678e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>108.765274</td>\n",
       "      <td>34.431236</td>\n",
       "      <td>0.41</td>\n",
       "      <td>449.58</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38168</th>\n",
       "      <td>1.585678e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>108.765274</td>\n",
       "      <td>34.431236</td>\n",
       "      <td>0.41</td>\n",
       "      <td>449.58</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38169</th>\n",
       "      <td>1.585678e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>108.765274</td>\n",
       "      <td>34.431236</td>\n",
       "      <td>0.41</td>\n",
       "      <td>449.58</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38170</th>\n",
       "      <td>1.585678e+09</td>\n",
       "      <td>AAR9751</td>\n",
       "      <td>108.765274</td>\n",
       "      <td>34.431236</td>\n",
       "      <td>0.41</td>\n",
       "      <td>449.58</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38171 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 时间      航班号          经度         纬度      速度       高度      航向\n",
       "0      1.585674e+09  AAR9751  112.957840  38.014561  726.38  7193.28  244.48\n",
       "1      1.585674e+09  AAR9751  112.957840  38.014561  726.38  7193.28  244.48\n",
       "2      1.585674e+09  AAR9751  112.953896  38.013103  726.38  7193.28  244.48\n",
       "3      1.585674e+09  AAR9751  112.953896  38.013103  726.38  7193.28  244.48\n",
       "4      1.585674e+09  AAR9751  112.949966  38.011600  726.38  7193.28  244.48\n",
       "...             ...      ...         ...        ...     ...      ...     ...\n",
       "38166  1.585678e+09  AAR9751  108.765274  34.431236    0.41   449.58    5.62\n",
       "38167  1.585678e+09  AAR9751  108.765274  34.431236    0.41   449.58    5.62\n",
       "38168  1.585678e+09  AAR9751  108.765274  34.431236    0.41   449.58    5.62\n",
       "38169  1.585678e+09  AAR9751  108.765274  34.431236    0.41   449.58    5.62\n",
       "38170  1.585678e+09  AAR9751  108.765274  34.431236    0.41   449.58    5.62\n",
       "\n",
       "[38171 rows x 7 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "475ea21d7e2ced9e016f53aa8edd27ecb1fe8de7c44be378c61a9513ecbbbc40"
  },
  "kernelspec": {
   "display_name": "Python 3.6.8 ('.venv': pipenv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
